﻿using Microsoft.Extensions.Configuration;
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace CommandSamples
{
    class Program
    {
        static void Main(string[] args)
        {
            if (args.Length != 1)
            {
                ShowUsage();
                return;
            }
            switch (args[0])
            {
                case "-nq":
                    ExecuteNonQuery();
                    break;
                case "-s":
                    ExecuteScalar();
                    break;
                case "-r":
                    ExecuteReader("Professional C#");
                    break;
                case "-sp":
                    StoredProcedure("Wrox Press");
                    break;
                default:
                    ShowUsage();
                    break;
            }

            Console.ReadLine();
        }

        public static void ShowUsage()
        {
            Console.WriteLine("ConnectionSamples command");
            Console.WriteLine("\t-nq\tExecute Non-Query");
            Console.WriteLine("\t-s\tExecute Scalar");
            Console.WriteLine("\t-r\tExecute Reader");
            Console.WriteLine("\t-sp\tStored Procedure");
        }

        /// <summary>
        /// 获取连接字符串
        /// </summary>
        /// <returns></returns>
        public static string GetConnectionString()
        {
            var configurationBuilder = new ConfigurationBuilder()
                .SetBasePath(Directory.GetCurrentDirectory())
                .AddJsonFile("config.json");

            IConfiguration config = configurationBuilder.Build();
            string connectionString = config["Data:DefaultConnection:ConnectionString"];
            return connectionString;
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="publisher"></param>
        private static void StoredProcedure(string publisher)
        {
            //使用连接字符串创建连接，在使用之后关闭连接
            using (var connection = new SqlConnection(GetConnectionString()))
            {
                //创建SQL命令对象
                SqlCommand command = connection.CreateCommand();
                //设置命令文本
                command.CommandText = "[ProCSharp].[GetBooksByPublisher]";
                //设置命令类型为存储过程
                command.CommandType = CommandType.StoredProcedure;
                
                //创建SQL参数对象
                SqlParameter p1 = command.CreateParameter();
                //设置p1参数 SQL类型
                p1.SqlDbType = SqlDbType.NVarChar;
                //设置p1参数变量名
                p1.ParameterName = "@Publisher";
                //设置p1参数值
                p1.Value = publisher;
                //将p1添加至命令参数中
                command.Parameters.Add(p1);

                //打开连接
                connection.Open();
                //执行命令
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    //读取结果
                    while (reader.Read())
                    {
                        int id = (int)reader["Id"];
                        string title = (string)reader["Title"];
                        string pub = (string)reader["Publisher"];
                        DateTime releaseDate = (DateTime)reader["ReleaseDate"];
                        Console.WriteLine($"{title} - {pub}; {releaseDate:d}");
                    }
                }
            }
        }

        /// <summary>
        /// 执行ExecuteScalar查询，
        /// 返回查询所返回的结果集中第一行的第一列。 忽略其他列或行。
        /// </summary>
        private static void ExecuteScalar()
        {
            //打开连接，执行完毕之后，关闭连接
            using (var connection = new SqlConnection(GetConnectionString()))
            {
                //创建sql命令字符串
                string sql = "SELECT COUNT(*) FROM [ProCSharp].[Books]";
                //基于connection连接，创建sql命令实例command
                SqlCommand command = connection.CreateCommand();
                //设置命令文本
                command.CommandText = sql;
                //打开连接
                connection.Open();
                //执行查询
                object count = command.ExecuteScalar();
                //输出查询结果
                Console.WriteLine($"counted {count} book records");
            }
        }

        /// <summary>
        /// 创建sql命令，并执行ExecuteReader，返回一个类型化的IDataReader
        /// </summary>
        public static void CreateCommand()
        {
            //创建并在完成任务后关闭连接
            using (var connection = new SqlConnection(GetConnectionString()))
            {
                
                string sql = "SELECT [Title], [Publisher], [ReleaseDate] FROM [ProCSharp].[Books]";
                //创建sql命令实例方法1：
                //SqlCommand构造函数注入sql文本，并创建命令实例
                var command = new SqlCommand(sql, connection);

                //创建sql命令实例方法2：
                //通过connection.CreateCommand创建，并通过相关属性设置sql文本和命令类型等等。
                //SqlCommand command2 = connection.CreateCommand();
                //command2.CommandText = sql;
                //command2.CommandType = CommandType.Text;

                //打开连接
                connection.Open();

                // ...
                //执行ExecuteReader
                SqlDataReader reader = command.ExecuteReader();
            }
        }

        /// <summary>
        /// 创建带参数的命令
        /// </summary>
        public static void CreateCommandWithParameters()
        {
            // //创建并在完成任务后关闭连接
            using (var connection = new SqlConnection(GetConnectionString()))
            {
                //创建命令实例
                string sql = "SELECT [Title], [Publisher], [ReleaseDate] FROM [ProCSharp].[Books] WHERE lower([Title]) LIKE @Title";
                var command = new SqlCommand(sql, connection);

                //创建命令参数变量及对应值--方法1
                command.Parameters.AddWithValue("Title", "Professional C#");
                //创建命令参数变量及对应值--方法2
                command.Parameters.Add("TitleStart", SqlDbType.NVarChar, 50);
                command.Parameters["Title"].Value = "Professional C#%";

                //SqlCommand command2 = connection.CreateCommand();
                //command2.CommandText = sql;
                //command2.CommandType = CommandType.Text;

                connection.Open();

                // ...

                SqlDataReader reader = command.ExecuteReader();
            }
        }

        /// <summary>
        /// 参数化命令的第3种方法
        /// </summary>
        public static void ExecuteCommand()
        {
            using (var connection = new SqlConnection(GetConnectionString()))
            {
                string sql = "SELECT [Title], [Publisher], [ReleaseDate] FROM [ProCSharp].[Books] WHERE lower([Title]) LIKE @Title ORDER BY [ReleaseDate]";
                var command = new SqlCommand(sql, connection);
                var parameter = new SqlParameter("Title", SqlDbType.NVarChar, 50)
                {
                    Value = "Professional C#"
                };
                command.Parameters.Add(parameter);

                //SqlCommand command2 = connection.CreateCommand();
                //command2.CommandText = sql;
                //command2.CommandType = CommandType.Text;

                connection.Open();

                // ...

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var s = reader[0];
                        Console.WriteLine(s);
                        //Console.WriteLine($"{reader.GetString(1)} {reader.GetString(2) ?? String.Empty}");
                    }
                }
            }
        }

        public static void ExecuteNonQuery()
        {
            try
            {
                using (var connection = new SqlConnection(GetConnectionString()))
                {
                    string sql = "INSERT INTO [ProCSharp].[Books] ([Title], [Publisher], [Isbn], [ReleaseDate]) " +
                        "VALUES (@Title, @Publisher, @Isbn, @ReleaseDate)";

                    //参数化命令的第4种写法
                    var command = new SqlCommand(sql, connection);
                    command.Parameters.AddWithValue("Title", "Professional C# 7 and .NET Core 2.0");
                    command.Parameters.AddWithValue("Publisher", "Wrox Press");
                    command.Parameters.AddWithValue("Isbn", " 978-1119449270");
                    command.Parameters.AddWithValue("ReleaseDate", new DateTime(2018, 4, 2));

                    //打开连接
                    connection.Open();
                    //执行无返回值的命令，只返回受影响的行数
                    int records = command.ExecuteNonQuery();

                    Console.WriteLine($"{records} record(s) inserted");
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        /// <summary>
        /// ExecuteReader
        /// </summary>
        /// <param name="title"></param>
        public static void ExecuteReader(string title)
        {
            string GetBookQuery() =>
                "SELECT [Id], [Title], [Publisher], [ReleaseDate] FROM [ProCSharp].[Books] WHERE lower([Title]) LIKE @Title ORDER BY [ReleaseDate] DESC";

            var connection = new SqlConnection(GetConnectionString());

            var command = new SqlCommand(GetBookQuery(), connection);
            var parameter = new SqlParameter("Title", SqlDbType.NVarChar, 50)
            {
                Value = $"{title}%"
                
            };
            command.Parameters.Add(parameter);

            connection.Open();

            using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
            {
                while (reader.Read())
                {
                    int id = reader.GetInt32(0);
                    string bookTitle = reader.GetString(1);
                    string publisher = reader[2].ToString();
                    DateTime? releaseDate = reader.IsDBNull(3) ? (DateTime?)null : reader.GetDateTime(3);
                    Console.WriteLine($"{id,5}. {bookTitle,-40} {publisher,-15} {releaseDate:d}");
                }
            }
        }
    }
}
